import sys
from sqlalchemy                  import *
from sqlalchemy.schema           import UniqueConstraint
from sqlalchemy                  import and_, or_
from sqlalchemy.ext.assignmapper import assign_mapper
import turbogears
from turbogears.database         import metadata, session
from kaizen.utils              import textilize
from genshi.builder              import tag
from genshi                      import Markup
import logging


log = logging.getLogger("kaizen.model")

projects = Table('project', metadata,
    Column('id', Integer, primary_key=True),
    Column('title', String(1000), nullable = False),
    Column('notes', String(1000)),
    Column('created', DateTime(), default=func.current_timestamp()),
    Column('modified', DateTime(), default=func.current_timestamp(), onupdate=func.current_timestamp()),
    Column('user', Integer, default=0),
    Column('closed', Boolean, default=0),
)

contexts = Table('context', metadata,
    Column('id', Integer, primary_key=True),
    Column('title', String(1000), nullable = False),
    Column('user', Integer, default=0),
    UniqueConstraint('title', 'user')
)

actions = Table('action', metadata,
    Column('id', Integer, primary_key=True),
    Column('title', String(1000), nullable = False),
    Column('project_id', Integer, ForeignKey('project.id')),
    Column('context_id', Integer, ForeignKey('context.id'), nullable = False),
    Column('notes', String(1000)),
    Column('priority', Integer, default=1),
    Column('closed', Boolean, default=0),
    Column('created', DateTime(), default=func.current_timestamp()),
    Column('modified', DateTime(), default=func.current_timestamp(), onupdate=func.current_timestamp()),
    Column('user', Integer, default=0),
)

notes = Table('note', metadata,
    Column('id', Integer, primary_key=True),
    Column('title', String(1000), nullable = False),
    Column('notes', String(1000)),
    Column('created', DateTime(), default=func.current_timestamp()),
    Column('modified', DateTime(), default=func.current_timestamp(), onupdate=func.current_timestamp()),
    Column('user', Integer, default=0)
)

tags = Table('tag', metadata,
    Column('id', Integer, primary_key=True),
    Column('title', String(1000), nullable = False, unique=True),
    Column('user', Integer, default=0)
)

properties = Table('property', metadata,
    Column('id', Integer, primary_key=True),
    Column('key', String(100), nullable = False),
    Column('value', String(100), nullable = False),
    Column('user', Integer, default=0),
    UniqueConstraint('key', 'user')
)

#tagaction = Table('tag_action', metadata,
#    Column('action_id', Integer, ForeignKey('action.id')),
#    Column('tag_id', Integer, ForeignKey('tag.id'))
#)

#tagproject = Table('tag_project', metadata,
#    Column('project_id', Integer, ForeignKey('project.id')),
#    Column('tag_id', Integer, ForeignKey('tag.id'))
#)

def create():
    metadata.create_all()
def drop():
    metadata.drop_all()

def getOpenActions(actions):
    result = []
    for action in actions:
        if not action.closed:
            result.append(action)
    return result
def getClosedActions(actions):
    result = []
    for action in actions:
        if action.closed:
            result.append(action)
    return result

def getTextilizedNotes(notes):
    text_ = textilize(notes)
    text = unicode(text_, 'utf-8', 'replace')
    return Markup(text)

class Project(object):
    def _get_closedActions(self):
        return getClosedActions(self.actions)
    def _get_openActions(self):
        return getOpenActions(self.actions)
    def _get_textilizedNotes(self):
        return getTextilizedNotes(self.notes)
    def __repr__(self):
        return ('<project id="%s" title="%s">' % (self.id, self.title)).encode(sys.stdout.encoding,'replace')

class Action(object):
    #def _get_separatedTags(self):
    #    separatedTags = ''
    #    for tag in self.tags:
    #        separatedTags = separatedTags + tag.title +','
    #    return str(separatedTags)
    def _get_textilizedNotes(self):
        return getTextilizedNotes(self.notes)
    def _get_ByContextAndPriority(self, context,priority):
        return session.query(Action).select(and_(Action.c.context==context, Action.c.priority==priority))
    def _get_ByProjectAndPriority(self, project,priority):
        return session.query(Action).select(and_(Action.c.project==project, Action.c.priority==priority))
    def __repr__(self):
        return ('<action id="s" title="s">')
class Note(object):
    def _get_textilizedNotes(self):
        return getTextilizedNotes(self.notes)
    def __repr__(self):
        return ('<note id="%s" title="%s">' % (self.id, self.title)).encode(sys.stdout.encoding,'replace')

class Tag(object):
    def __repr__(self):
        return ('<tag id="%s" title="%s">' % (self.id, self.title)).encode(sys.stdout.encoding,'replace')

class Priority(object):
    def __repr__(self):
        return ('<priority title="%s" value="%s">' % (self.title, self.value))

class Context(object):
    def _get_closedActions(self):
        return getClosedActions(self.actions)
    def _get_openActions(self):
        return getOpenActions(self.actions)
    def __repr__(self):
        return ('<context id="%s" title="%s">' % (self.id, self.title)).encode(sys.stdout.encoding,'replace')

class Property(object):
    def __repr__(self):
        return ('<property key="%s" value="%s">' % (self.key, self.value))

# define a mapper that will load 'user' in separate, individual-row SELECT statements
#when each attribute is first referenced on the individual object instance
#tagmapper = assign_mapper(session.context, Tag, tags, properties = {
#    'user' : deferred(tags.c.user)
#})
notemapper = assign_mapper(session.context, Note, notes, properties = {
})
actionmapper = assign_mapper(session.context, Action, actions, properties = {
#    'tags' : relation(Tag, secondary=tagaction, lazy=True, cascade="all, delete-orphan")

})

projectmapper = assign_mapper(session.context, Project, projects, properties={
    'actions' : relation(Action, backref=backref('project',lazy=True), order_by=actions.c.priority, cascade="all"),
#    'tags' : relation(Tag, secondary=tagproject, cascade="all, delete-orphan")
})


contextmapper = assign_mapper(session.context, Context, contexts, properties={
    'actions' : relation(Action, backref='context', cascade="all, delete-orphan"),
    'user' : deferred(contexts.c.user)
})

propertymapper = assign_mapper(session.context, Property, properties, properties = {
})


